-- GitLab时序数据库表结构设计
-- 数据库: TimescaleDB (PostgreSQL扩展)
-- 连接: postgresql://postgres:water@10.48.0.85:5432/gitlab_stats

-- 创建扩展（如果尚未创建）
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 用户别名映射表（用于数据清洗）
CREATE TABLE IF NOT EXISTS user_name_aliases (
    alias_name VARCHAR(255) NOT NULL,     -- 别名（实际在commits中出现的名字）
    canonical_name VARCHAR(255) NOT NULL, -- 标准名（统一后的名字）
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (alias_name)
);

-- 插入已知的别名映射关系
INSERT INTO user_name_aliases (alias_name, canonical_name) VALUES
    ('xiong', 'zhe.xiong'),
    ('zhe.xiong', 'zhe.xiong'),
    ('包强', 'baoqiang'),
    ('baoqiang', 'baoqiang'),
    ('wei.wang', 'wangwei'),
    ('“hjx”', 'hjx'),
    ('Wenyu Ouyang', 'ouyangwenyu'),
    ('liangkai', 'kai.liang'),
    ('gaoyussdut', 'gaoyu'),
    ('yu.guo', 'guoyu'),
    ('hy','hongyu.liu'),
    ('孙静静','jingjing.sun'),
    ('hans','menghan.yan')
ON CONFLICT (alias_name) DO UPDATE SET
    canonical_name = EXCLUDED.canonical_name,
    updated_at = NOW();

-- 用户别名映射函数
CREATE OR REPLACE FUNCTION get_canonical_name(input_name VARCHAR(255))
RETURNS VARCHAR(255) AS $$
BEGIN
    RETURN COALESCE(
        (SELECT canonical_name FROM user_name_aliases WHERE alias_name = input_name),
        input_name
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 1. GitLab项目信息表
CREATE TABLE IF NOT EXISTS gitlab_projects (
    gitlab_url VARCHAR(255) NOT NULL, -- GitLab实例URL
    project_id INTEGER NOT NULL,
    project_name VARCHAR(255) NOT NULL,
    project_path VARCHAR(255) NOT NULL,
    project_path_with_namespace VARCHAR(500) NOT NULL,
    project_description TEXT,
    default_branch VARCHAR(100),
    visibility VARCHAR(20) DEFAULT 'private',
    web_url TEXT,
    ssh_url_to_repo TEXT,
    http_url_to_repo TEXT,
    namespace_name VARCHAR(255),
    namespace_path VARCHAR(255),
    owner_name VARCHAR(255),
    owner_username VARCHAR(255),
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ,
    last_activity_at TIMESTAMPTZ,
    star_count INTEGER DEFAULT 0,
    forks_count INTEGER DEFAULT 0,
    issues_enabled BOOLEAN DEFAULT false,
    merge_requests_enabled BOOLEAN DEFAULT false,
    wiki_enabled BOOLEAN DEFAULT false,
    archived BOOLEAN DEFAULT false,
    empty_repo BOOLEAN DEFAULT false,
    sync_time TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (gitlab_url, project_id)
);

-- 2. GitLab用户信息表
CREATE TABLE IF NOT EXISTS gitlab_users (
    gitlab_url VARCHAR(255) NOT NULL, -- GitLab实例URL
    user_id INTEGER NOT NULL,
    username VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    email VARCHAR(255),
    public_email VARCHAR(255),
    private_email VARCHAR(255),
    state VARCHAR(50),
    avatar_url TEXT,
    web_url TEXT,
    bio TEXT,
    location VARCHAR(255),
    organization VARCHAR(255),
    job_title VARCHAR(255),
    created_at TIMESTAMPTZ,
    last_sign_in_at TIMESTAMPTZ,
    last_activity_on DATE,
    projects_limit INTEGER,
    can_create_group BOOLEAN,
    can_create_project BOOLEAN,
    two_factor_enabled BOOLEAN,
    external BOOLEAN,
    private_profile BOOLEAN,
    sync_time TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (gitlab_url, user_id),
    UNIQUE (gitlab_url, username)
);

-- 3. GitLab提交时序数据表（主表）
CREATE TABLE IF NOT EXISTS gitlab_commits (
    -- 复合主键：GitLab URL + 项目ID + 分支 + 提交时间 + 提交SHA
    gitlab_url VARCHAR(255) NOT NULL, -- GitLab实例URL
    project_branch_key VARCHAR(500) NOT NULL, -- 格式: "gitlab_url:project_id:branch_name"
    commit_time TIMESTAMPTZ NOT NULL,
    commit_sha VARCHAR(40) NOT NULL,

    -- 项目和分支信息
    project_id INTEGER NOT NULL,
    project_name VARCHAR(255),
    project_path_with_namespace VARCHAR(500),
    branch_name VARCHAR(255) NOT NULL,

    -- 提交基本信息
    commit_title TEXT,
    commit_message TEXT,
    commit_short_id VARCHAR(8),

    -- 作者信息
    author_name VARCHAR(255),
    author_email VARCHAR(255),
    author_username VARCHAR(255), -- GitLab用户名（通过邮箱映射获得）
    author_user_id INTEGER, -- GitLab用户ID

    -- 提交者信息（可能与作者不同）
    committer_name VARCHAR(255),
    committer_email VARCHAR(255),
    committer_username VARCHAR(255),
    committer_user_id INTEGER,

    -- 代码统计信息
    additions INTEGER DEFAULT 0, -- 新增行数
    deletions INTEGER DEFAULT 0, -- 删除行数
    total_changes INTEGER DEFAULT 0, -- 总变更行数

    -- 文件统计信息
    files_changed INTEGER DEFAULT 0, -- 变更文件数

    -- 提交类型和标签
    commit_type VARCHAR(50), -- feat, fix, docs, style, refactor, test, chore等
    is_merge_commit BOOLEAN DEFAULT false,
    parent_ids TEXT[], -- 父提交ID数组

    -- 时间信息
    authored_date TIMESTAMPTZ, -- 作者提交时间
    committed_date TIMESTAMPTZ, -- 提交者提交时间

    -- 元数据
    sync_time TIMESTAMPTZ DEFAULT NOW(), -- 数据同步时间
    data_source VARCHAR(50) DEFAULT 'gitlab_api', -- 数据来源

    -- 主键约束
    PRIMARY KEY (gitlab_url, project_branch_key, commit_time, commit_sha)
);

-- 将gitlab_commits表转换为时序表（TimescaleDB特性）
SELECT create_hypertable('gitlab_commits', 'commit_time',
    chunk_time_interval => INTERVAL '1 month',
    if_not_exists => TRUE,
    migrate_data => TRUE
);

-- 4. 分支信息表
CREATE TABLE IF NOT EXISTS gitlab_branches (
    gitlab_url VARCHAR(255) NOT NULL, -- GitLab实例URL
    project_id INTEGER NOT NULL,
    branch_name VARCHAR(255) NOT NULL,
    branch_commit_sha VARCHAR(40),
    branch_commit_title TEXT,
    branch_commit_message TEXT,
    branch_commit_author_name VARCHAR(255),
    branch_commit_author_email VARCHAR(255),
    branch_commit_date TIMESTAMPTZ,
    is_default BOOLEAN DEFAULT false,
    is_protected BOOLEAN DEFAULT false,
    can_push BOOLEAN DEFAULT true,
    developers_can_push BOOLEAN DEFAULT true,
    developers_can_merge BOOLEAN DEFAULT true,
    sync_time TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (gitlab_url, project_id, branch_name)
);

-- 5. 每日统计汇总表（用于快速查询）
CREATE TABLE IF NOT EXISTS gitlab_daily_stats (
    gitlab_url VARCHAR(255) NOT NULL, -- GitLab实例URL
    stat_date DATE NOT NULL,
    project_branch_key VARCHAR(500) NOT NULL,
    project_id INTEGER NOT NULL,
    project_name VARCHAR(255),
    branch_name VARCHAR(255) NOT NULL,

    -- 提交统计
    total_commits INTEGER DEFAULT 0,
    unique_authors INTEGER DEFAULT 0,

    -- 代码统计
    total_additions INTEGER DEFAULT 0,
    total_deletions INTEGER DEFAULT 0,
    total_changes INTEGER DEFAULT 0,
    total_files_changed INTEGER DEFAULT 0,

    -- 作者列表（JSON格式）
    authors_list JSONB,

    -- 元数据
    sync_time TIMESTAMPTZ DEFAULT NOW(),

    PRIMARY KEY (gitlab_url, stat_date, project_branch_key)
);

-- 将daily_stats表也转换为时序表
SELECT create_hypertable('gitlab_daily_stats', 'stat_date',
    chunk_time_interval => INTERVAL '3 months',
    if_not_exists => TRUE,
    migrate_data => TRUE
);

-- 6. 同步状态表（记录增量同步的进度）
CREATE TABLE IF NOT EXISTS gitlab_sync_status (
    gitlab_url VARCHAR(255) NOT NULL, -- GitLab实例URL
    project_id INTEGER NOT NULL,
    branch_name VARCHAR(255) NOT NULL,
    last_sync_time TIMESTAMPTZ,
    last_commit_time TIMESTAMPTZ,
    last_commit_sha VARCHAR(40),
    sync_type VARCHAR(20) DEFAULT 'incremental', -- full, incremental
    sync_status VARCHAR(20) DEFAULT 'pending', -- pending, running, completed, failed
    error_message TEXT,
    total_commits_synced INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (gitlab_url, project_id, branch_name)
);

-- 创建索引

-- gitlab_commits表的索引
CREATE INDEX IF NOT EXISTS idx_gitlab_commits_gitlab_url ON gitlab_commits (gitlab_url);
CREATE INDEX IF NOT EXISTS idx_gitlab_commits_project_id ON gitlab_commits (gitlab_url, project_id);
CREATE INDEX IF NOT EXISTS idx_gitlab_commits_branch_name ON gitlab_commits (gitlab_url, branch_name);
CREATE INDEX IF NOT EXISTS idx_gitlab_commits_author_email ON gitlab_commits (author_email);
CREATE INDEX IF NOT EXISTS idx_gitlab_commits_author_username ON gitlab_commits (author_username);
CREATE INDEX IF NOT EXISTS idx_gitlab_commits_commit_time ON gitlab_commits (commit_time DESC);
CREATE INDEX IF NOT EXISTS idx_gitlab_commits_sync_time ON gitlab_commits (sync_time);
CREATE INDEX IF NOT EXISTS idx_gitlab_commits_project_branch ON gitlab_commits (gitlab_url, project_id, branch_name);

-- gitlab_daily_stats表的索引
CREATE INDEX IF NOT EXISTS idx_gitlab_daily_stats_gitlab_url ON gitlab_daily_stats (gitlab_url);
CREATE INDEX IF NOT EXISTS idx_gitlab_daily_stats_project_id ON gitlab_daily_stats (gitlab_url, project_id);
CREATE INDEX IF NOT EXISTS idx_gitlab_daily_stats_branch_name ON gitlab_daily_stats (gitlab_url, branch_name);
CREATE INDEX IF NOT EXISTS idx_gitlab_daily_stats_date ON gitlab_daily_stats (stat_date DESC);

-- gitlab_projects表的索引
CREATE INDEX IF NOT EXISTS idx_gitlab_projects_gitlab_url ON gitlab_projects (gitlab_url);
CREATE INDEX IF NOT EXISTS idx_gitlab_projects_path ON gitlab_projects (gitlab_url, project_path_with_namespace);
CREATE INDEX IF NOT EXISTS idx_gitlab_projects_updated ON gitlab_projects (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_gitlab_projects_activity ON gitlab_projects (last_activity_at DESC);

-- gitlab_users表的索引
CREATE INDEX IF NOT EXISTS idx_gitlab_users_gitlab_url ON gitlab_users (gitlab_url);
CREATE INDEX IF NOT EXISTS idx_gitlab_users_email ON gitlab_users (gitlab_url, email);
CREATE INDEX IF NOT EXISTS idx_gitlab_users_username ON gitlab_users (gitlab_url, username);
CREATE INDEX IF NOT EXISTS idx_gitlab_users_updated ON gitlab_users (last_sign_in_at DESC);

-- gitlab_sync_status表的索引
CREATE INDEX IF NOT EXISTS idx_gitlab_sync_status_updated ON gitlab_sync_status (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_gitlab_sync_status_last_sync ON gitlab_sync_status (last_sync_time DESC);

-- 创建外键约束
ALTER TABLE gitlab_commits
    ADD CONSTRAINT fk_gitlab_commits_project
    FOREIGN KEY (gitlab_url, project_id) REFERENCES gitlab_projects(gitlab_url, project_id)
    ON DELETE CASCADE;

ALTER TABLE gitlab_branches
    ADD CONSTRAINT fk_gitlab_branches_project
    FOREIGN KEY (gitlab_url, project_id) REFERENCES gitlab_projects(gitlab_url, project_id)
    ON DELETE CASCADE;

ALTER TABLE gitlab_daily_stats
    ADD CONSTRAINT fk_gitlab_daily_stats_project
    FOREIGN KEY (gitlab_url, project_id) REFERENCES gitlab_projects(gitlab_url, project_id)
    ON DELETE CASCADE;

ALTER TABLE gitlab_sync_status
    ADD CONSTRAINT fk_gitlab_sync_status_project
    FOREIGN KEY (gitlab_url, project_id) REFERENCES gitlab_projects(gitlab_url, project_id)
    ON DELETE CASCADE;

-- 创建视图用于常用查询

-- 1. 项目分支提交统计视图
CREATE OR REPLACE VIEW v_project_branch_stats AS
SELECT
    gitlab_url,
    project_id,
    project_name,
    branch_name,
    COUNT(*) as total_commits,
    COUNT(DISTINCT author_email) as unique_authors,
    SUM(additions) as total_additions,
    SUM(deletions) as total_deletions,
    SUM(total_changes) as total_changes,
    MIN(commit_time) as first_commit_time,
    MAX(commit_time) as last_commit_time,
    MAX(sync_time) as last_sync_time
FROM gitlab_commits
WHERE NOT is_merge_commit
GROUP BY gitlab_url, project_id, project_name, branch_name;

-- 2. 员工资质表
DROP TABLE IF EXISTS employee_qualifications CASCADE;
CREATE TABLE employee_qualifications (
    name TEXT PRIMARY KEY,
    education INTEGER DEFAULT 50,  -- 教育背景权重 (30-90)
    technical_skill INTEGER DEFAULT 50,  -- 技术能力权重 (40-95)
    leadership INTEGER DEFAULT 50  -- 领导力权重 (30-70)
);


-- 3. 用户贡献统计视图（跨GitLab实例合并统计）
-- 删除旧的视图（如果存在）
DROP MATERIALIZED VIEW IF EXISTS v_user_contribution_stats;

-- 创建物化视图
-- public.v_user_contribution_stats source
CREATE MATERIALIZED VIEW public.v_user_contribution_stats
TABLESPACE pg_default
AS WITH user_name_stats AS (
         SELECT gitlab_commits.author_email,
            gitlab_commits.author_username,
            gitlab_commits.author_name,
            count(*) AS name_usage_count,
            row_number() OVER (PARTITION BY gitlab_commits.author_email ORDER BY (count(*)) DESC, (max(gitlab_commits.committed_date)) DESC) AS rn
           FROM gitlab_commits
          WHERE gitlab_commits.author_email IS NOT NULL AND NOT gitlab_commits.is_merge_commit AND (gitlab_commits.additions >= 10 OR gitlab_commits.deletions >= 10)
          GROUP BY gitlab_commits.author_email, gitlab_commits.author_username, gitlab_commits.author_name
        ), preferred_names AS (
         SELECT user_name_stats.author_email,
            user_name_stats.author_username,
            user_name_stats.author_name
           FROM user_name_stats
          WHERE user_name_stats.rn = 1
        ), name_dedup_stats AS (
         SELECT get_canonical_name(p_1.author_name) AS canonical_name,
            string_agg(DISTINCT p_1.author_name::text, '; '::text ORDER BY (p_1.author_name::text)) AS original_names,
            count(DISTINCT p_1.author_email) AS email_count,
            count(DISTINCT p_1.author_username) AS username_count,
            string_agg(DISTINCT p_1.author_email::text, '; '::text ORDER BY (p_1.author_email::text)) AS all_emails,
            string_agg(DISTINCT p_1.author_username::text, '; '::text ORDER BY (p_1.author_username::text)) AS all_usernames
           FROM preferred_names p_1
          GROUP BY (get_canonical_name(p_1.author_name))
        ), commit_type_stats AS (
         SELECT get_canonical_name(p_1.author_name) AS canonical_name,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(feat|feature)'::text THEN 1
                    ELSE NULL::integer
                END) AS feature_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(fix|bugfix)'::text THEN 1
                    ELSE NULL::integer
                END) AS bugfix_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(docs|doc)'::text THEN 1
                    ELSE NULL::integer
                END) AS doc_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(test|tests)'::text THEN 1
                    ELSE NULL::integer
                END) AS test_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(refactor|refact)'::text THEN 1
                    ELSE NULL::integer
                END) AS refactor_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(style|format)'::text THEN 1
                    ELSE NULL::integer
                END) AS style_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(chore|build|ci)'::text THEN 1
                    ELSE NULL::integer
                END) AS chore_commits
           FROM preferred_names p_1
             JOIN gitlab_commits c_1 ON p_1.author_email::text = c_1.author_email::text
          WHERE c_1.author_email IS NOT NULL AND NOT c_1.is_merge_commit
          GROUP BY (get_canonical_name(p_1.author_name))
        ), time_distribution_stats AS (
         SELECT get_canonical_name(p_1.author_name) AS canonical_name,
            count(
                CASE
                    WHEN EXTRACT(hour FROM c_1.committed_date) >= 9::numeric AND EXTRACT(hour FROM c_1.committed_date) <= 17::numeric THEN 1
                    ELSE NULL::integer
                END) AS work_hours_commits,
            count(
                CASE
                    WHEN EXTRACT(hour FROM c_1.committed_date) < 9::numeric OR EXTRACT(hour FROM c_1.committed_date) > 17::numeric THEN 1
                    ELSE NULL::integer
                END) AS off_hours_commits,
            count(
                CASE
                    WHEN EXTRACT(dow FROM c_1.committed_date) = ANY (ARRAY[0::numeric, 6::numeric]) THEN 1
                    ELSE NULL::integer
                END) AS weekend_commits,
            count(
                CASE
                    WHEN EXTRACT(dow FROM c_1.committed_date) >= 1::numeric AND EXTRACT(dow FROM c_1.committed_date) <= 5::numeric THEN 1
                    ELSE NULL::integer
                END) AS weekday_commits,
            count(DISTINCT EXTRACT(hour FROM c_1.committed_date)) AS active_hours_count,
            count(DISTINCT EXTRACT(dow FROM c_1.committed_date)) AS active_days_of_week
           FROM preferred_names p_1
             JOIN gitlab_commits c_1 ON p_1.author_email::text = c_1.author_email::text
          WHERE c_1.author_email IS NOT NULL AND NOT c_1.is_merge_commit
          GROUP BY (get_canonical_name(p_1.author_name))
        ), project_contribution_stats AS (
         SELECT get_canonical_name(p_1.author_name) AS canonical_name,
            count(DISTINCT c_1.project_id) AS total_projects,
            max(project_commits.commit_count) AS max_commits_per_project,
            min(project_commits.commit_count) AS min_commits_per_project,
            round(avg(project_commits.commit_count), 2) AS avg_commits_per_project,
            count(
                CASE
                    WHEN project_commits.commit_count >= 10 THEN 1
                    ELSE NULL::integer
                END) AS significant_projects,
            string_agg(DISTINCT gp.project_name::text, '; '::text ORDER BY (gp.project_name::text)) AS project_names
           FROM preferred_names p_1
             JOIN gitlab_commits c_1 ON p_1.author_email::text = c_1.author_email::text
             JOIN gitlab_projects gp ON c_1.gitlab_url::text = gp.gitlab_url::text AND c_1.project_id = gp.project_id
             JOIN ( SELECT get_canonical_name(p2.author_name) AS canonical_name,
                    c2.project_id,
                    count(*) AS commit_count
                   FROM preferred_names p2
                     JOIN gitlab_commits c2 ON p2.author_email::text = c2.author_email::text
                  WHERE c2.author_email IS NOT NULL AND NOT c2.is_merge_commit
                  GROUP BY (get_canonical_name(p2.author_name)), c2.project_id) project_commits ON get_canonical_name(p_1.author_name)::text = project_commits.canonical_name::text AND c_1.project_id = project_commits.project_id
          WHERE c_1.author_email IS NOT NULL AND NOT c_1.is_merge_commit
          GROUP BY (get_canonical_name(p_1.author_name))
        )
 SELECT nd.canonical_name,
    nd.original_names,
    nd.email_count,
    nd.username_count,
    nd.all_emails,
    nd.all_usernames,
    count(*) AS total_commits,
    count(DISTINCT c.project_id) AS projects_count,
    count(DISTINCT c.branch_name) AS branches_count,
    count(DISTINCT date(c.committed_date)) AS active_days,
    sum(c.additions) AS total_additions,
    sum(c.deletions) AS total_deletions,
    sum(c.total_changes) AS total_changes,
    min(c.committed_date) AS first_commit_time,
    max(c.committed_date) AS last_commit_time,
    round(
        CASE
            WHEN min(c.committed_date) = max(c.committed_date) THEN count(*)::numeric * 365::numeric
            ELSE count(*)::numeric / GREATEST(EXTRACT(epoch FROM max(c.committed_date) - min(c.committed_date)) / 86400::numeric, 1::numeric) * 365::numeric
        END, 2) AS annual_commit_rate,
    round(
        CASE
            WHEN min(c.committed_date) = max(c.committed_date) THEN count(*)::numeric * 30::numeric
            ELSE count(*)::numeric / GREATEST(EXTRACT(epoch FROM max(c.committed_date) - min(c.committed_date)) / 86400::numeric, 1::numeric) * 30::numeric
        END, 2) AS month_commit_rate,
    round(
        CASE
            WHEN min(c.committed_date) = max(c.committed_date) THEN sum(c.additions)::numeric * 365::numeric
            ELSE sum(c.additions)::numeric / GREATEST(EXTRACT(epoch FROM max(c.committed_date) - min(c.committed_date)) / 86400::numeric, 1::numeric) * 365::numeric
        END, 2) AS annual_total_additions,
    round(
        CASE
            WHEN min(c.committed_date) = max(c.committed_date) THEN sum(c.additions)::numeric * 30::numeric
            ELSE sum(c.additions)::numeric / GREATEST(EXTRACT(epoch FROM max(c.committed_date) - min(c.committed_date)) / 86400::numeric, 1::numeric) * 30::numeric
        END, 2) AS month_total_additions,
    round(sum(c.additions)::numeric / NULLIF(count(*), 0)::numeric, 2) AS avg_additions_per_commit,
    round(sum(c.deletions)::numeric / NULLIF(count(*), 0)::numeric, 2) AS avg_deletions_per_commit,
    round(sum(c.total_changes)::numeric / NULLIF(count(*), 0)::numeric, 2) AS avg_changes_per_commit,
    round(sum(c.files_changed)::numeric / NULLIF(count(*), 0)::numeric, 2) AS avg_files_per_commit,
    round(sum(c.additions)::numeric / NULLIF(sum(c.additions + c.deletions), 0)::numeric * 100::numeric, 2) AS addition_ratio_percent,
    COALESCE(cts.feature_commits, 0::bigint) AS feature_commits,
    COALESCE(cts.bugfix_commits, 0::bigint) AS bugfix_commits,
    COALESCE(cts.doc_commits, 0::bigint) AS doc_commits,
    COALESCE(cts.test_commits, 0::bigint) AS test_commits,
    COALESCE(cts.refactor_commits, 0::bigint) AS refactor_commits,
    COALESCE(cts.style_commits, 0::bigint) AS style_commits,
    COALESCE(cts.chore_commits, 0::bigint) AS chore_commits,
    COALESCE(tds.work_hours_commits, 0::bigint) AS work_hours_commits,
    COALESCE(tds.off_hours_commits, 0::bigint) AS off_hours_commits,
    COALESCE(tds.weekend_commits, 0::bigint) AS weekend_commits,
    COALESCE(tds.weekday_commits, 0::bigint) AS weekday_commits,
    COALESCE(tds.active_hours_count, 0::bigint) AS active_hours_count,
    COALESCE(tds.active_days_of_week, 0::bigint) AS active_days_of_week,
    round(COALESCE(tds.work_hours_commits, 0::bigint)::numeric / NULLIF(count(*), 0)::numeric * 100::numeric, 2) AS work_hours_ratio_percent,
    round(COALESCE(tds.weekend_commits, 0::bigint)::numeric / NULLIF(count(*), 0)::numeric * 100::numeric, 2) AS weekend_ratio_percent,
    COALESCE(pcs.total_projects, 0::bigint) AS total_projects,
    COALESCE(pcs.max_commits_per_project, 0::bigint) AS max_commits_per_project,
    COALESCE(pcs.min_commits_per_project, 0::bigint) AS min_commits_per_project,
    COALESCE(pcs.avg_commits_per_project, 0::numeric) AS avg_commits_per_project,
    COALESCE(pcs.significant_projects, 0::bigint) AS significant_projects,
    COALESCE(pcs.project_names, ''::text) AS project_names,
    round(count(DISTINCT date(c.committed_date))::numeric / NULLIF(GREATEST(EXTRACT(epoch FROM max(c.committed_date) - min(c.committed_date)) / 86400::numeric, 1::numeric), 0::numeric) * 100::numeric, 2) AS consistency_ratio_percent,
    count(DISTINCT c.project_id) AS collaboration_projects_count,
    COALESCE(pcs.avg_commits_per_project, 0::numeric) AS avg_project_contribution,
    round((COALESCE(pcs.max_commits_per_project, 0::bigint) - COALESCE(pcs.min_commits_per_project, 0::bigint))::numeric, 2) AS project_contribution_variance,
    count(DISTINCT EXTRACT(year FROM c.committed_date)) AS active_years_count,
    round(count(DISTINCT c.project_id)::numeric / NULLIF(count(DISTINCT EXTRACT(year FROM c.committed_date)), 0)::numeric, 2) AS projects_per_year,
    round(sum(c.additions)::numeric / NULLIF(count(DISTINCT c.project_id), 0)::numeric, 2) AS avg_additions_per_project,
        CASE
            WHEN count(DISTINCT c.project_id) >= 5 AND count(*) >= 200 THEN 'Tech Lead'::text
            WHEN count(*) >= 100 AND count(DISTINCT c.project_id) >= 3 THEN 'Senior'::text
            WHEN count(*) >= 50 AND count(DISTINCT c.project_id) >= 2 THEN 'Intermediate'::text
            WHEN count(*) >= 10 THEN 'Junior'::text
            ELSE 'Beginner'::text
        END AS contribution_level,
    COALESCE(eq.education, 50) AS education,
    COALESCE(eq.technical_skill, 50) AS technical_skill,
    COALESCE(eq.leadership, 50) AS leadership
   FROM name_dedup_stats nd
     JOIN preferred_names p ON get_canonical_name(p.author_name)::text = nd.canonical_name::text
     JOIN gitlab_commits c ON p.author_email::text = c.author_email::text
     LEFT JOIN commit_type_stats cts ON nd.canonical_name::text = cts.canonical_name::text
     LEFT JOIN time_distribution_stats tds ON nd.canonical_name::text = tds.canonical_name::text
     LEFT JOIN project_contribution_stats pcs ON nd.canonical_name::text = pcs.canonical_name::text
     LEFT JOIN employee_qualifications eq ON nd.canonical_name::text = eq.name
  WHERE c.author_email IS NOT NULL AND NOT c.is_merge_commit
  GROUP BY nd.canonical_name, nd.original_names, nd.email_count, nd.username_count, nd.all_emails, nd.all_usernames, cts.feature_commits, cts.bugfix_commits, cts.doc_commits, cts.test_commits, cts.refactor_commits, cts.style_commits, cts.chore_commits, tds.work_hours_commits, tds.off_hours_commits, tds.weekend_commits, tds.weekday_commits, tds.active_hours_count, tds.active_days_of_week, pcs.total_projects, pcs.max_commits_per_project, pcs.min_commits_per_project, pcs.avg_commits_per_project, pcs.significant_projects, pcs.project_names, (COALESCE(eq.education, 50)), (COALESCE(eq.technical_skill, 50)), (COALESCE(eq.leadership, 50))
WITH DATA;

-- 创建半年内数据的物化视图
-- public.v_user_contribution_stats_6m source
CREATE MATERIALIZED VIEW public.v_user_contribution_stats_6m
TABLESPACE pg_default
AS WITH user_name_stats AS (
         SELECT gitlab_commits.author_email,
            gitlab_commits.author_username,
            gitlab_commits.author_name,
            count(*) AS name_usage_count,
            row_number() OVER (PARTITION BY gitlab_commits.author_email ORDER BY (count(*)) DESC, (max(gitlab_commits.committed_date)) DESC) AS rn
           FROM gitlab_commits
          WHERE gitlab_commits.author_email IS NOT NULL AND NOT gitlab_commits.is_merge_commit AND gitlab_commits.committed_date >= (CURRENT_DATE - '6 mons'::interval) AND (gitlab_commits.additions >= 10 OR gitlab_commits.deletions >= 10)
          GROUP BY gitlab_commits.author_email, gitlab_commits.author_username, gitlab_commits.author_name
        ), preferred_names AS (
         SELECT user_name_stats.author_email,
            user_name_stats.author_username,
            user_name_stats.author_name
           FROM user_name_stats
          WHERE user_name_stats.rn = 1
        ), name_dedup_stats AS (
         SELECT get_canonical_name(p_1.author_name) AS canonical_name,
            string_agg(DISTINCT p_1.author_name::text, '; '::text ORDER BY (p_1.author_name::text)) AS original_names,
            count(DISTINCT p_1.author_email) AS email_count,
            count(DISTINCT p_1.author_username) AS username_count,
            string_agg(DISTINCT p_1.author_email::text, '; '::text ORDER BY (p_1.author_email::text)) AS all_emails,
            string_agg(DISTINCT p_1.author_username::text, '; '::text ORDER BY (p_1.author_username::text)) AS all_usernames
           FROM preferred_names p_1
          GROUP BY (get_canonical_name(p_1.author_name))
        ), commit_type_stats AS (
         SELECT get_canonical_name(p_1.author_name) AS canonical_name,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(feat|feature)'::text THEN 1
                    ELSE NULL::integer
                END) AS feature_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(fix|bugfix)'::text THEN 1
                    ELSE NULL::integer
                END) AS bugfix_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(docs|doc)'::text THEN 1
                    ELSE NULL::integer
                END) AS doc_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(test|tests)'::text THEN 1
                    ELSE NULL::integer
                END) AS test_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(refactor|refact)'::text THEN 1
                    ELSE NULL::integer
                END) AS refactor_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(style|format)'::text THEN 1
                    ELSE NULL::integer
                END) AS style_commits,
            count(
                CASE
                    WHEN c_1.commit_message ~* '^(chore|build|ci)'::text THEN 1
                    ELSE NULL::integer
                END) AS chore_commits
           FROM preferred_names p_1
             JOIN gitlab_commits c_1 ON p_1.author_email::text = c_1.author_email::text
          WHERE c_1.author_email IS NOT NULL AND NOT c_1.is_merge_commit AND c_1.committed_date >= (CURRENT_DATE - '6 mons'::interval)
          GROUP BY (get_canonical_name(p_1.author_name))
        ), time_distribution_stats AS (
         SELECT get_canonical_name(p_1.author_name) AS canonical_name,
            count(
                CASE
                    WHEN EXTRACT(hour FROM c_1.committed_date) >= 9::numeric AND EXTRACT(hour FROM c_1.committed_date) <= 17::numeric THEN 1
                    ELSE NULL::integer
                END) AS work_hours_commits,
            count(
                CASE
                    WHEN EXTRACT(hour FROM c_1.committed_date) < 9::numeric OR EXTRACT(hour FROM c_1.committed_date) > 17::numeric THEN 1
                    ELSE NULL::integer
                END) AS off_hours_commits,
            count(
                CASE
                    WHEN EXTRACT(dow FROM c_1.committed_date) = ANY (ARRAY[0::numeric, 6::numeric]) THEN 1
                    ELSE NULL::integer
                END) AS weekend_commits,
            count(
                CASE
                    WHEN EXTRACT(dow FROM c_1.committed_date) >= 1::numeric AND EXTRACT(dow FROM c_1.committed_date) <= 5::numeric THEN 1
                    ELSE NULL::integer
                END) AS weekday_commits,
            count(DISTINCT EXTRACT(hour FROM c_1.committed_date)) AS active_hours_count,
            count(DISTINCT EXTRACT(dow FROM c_1.committed_date)) AS active_days_of_week
           FROM preferred_names p_1
             JOIN gitlab_commits c_1 ON p_1.author_email::text = c_1.author_email::text
          WHERE c_1.author_email IS NOT NULL AND NOT c_1.is_merge_commit AND c_1.committed_date >= (CURRENT_DATE - '6 mons'::interval)
          GROUP BY (get_canonical_name(p_1.author_name))
        ), project_contribution_stats AS (
         SELECT get_canonical_name(p_1.author_name) AS canonical_name,
            count(DISTINCT c_1.project_id) AS total_projects,
            max(project_commits.commit_count) AS max_commits_per_project,
            min(project_commits.commit_count) AS min_commits_per_project,
            round(avg(project_commits.commit_count), 2) AS avg_commits_per_project,
            count(
                CASE
                    WHEN project_commits.commit_count >= 10 THEN 1
                    ELSE NULL::integer
                END) AS significant_projects,
            string_agg(DISTINCT gp.project_name::text, '; '::text ORDER BY (gp.project_name::text)) AS project_names
           FROM preferred_names p_1
             JOIN gitlab_commits c_1 ON p_1.author_email::text = c_1.author_email::text
             JOIN gitlab_projects gp ON c_1.gitlab_url::text = gp.gitlab_url::text AND c_1.project_id = gp.project_id
             JOIN ( SELECT get_canonical_name(p2.author_name) AS canonical_name,
                    c2.project_id,
                    count(*) AS commit_count
                   FROM preferred_names p2
                     JOIN gitlab_commits c2 ON p2.author_email::text = c2.author_email::text
                  WHERE c2.author_email IS NOT NULL AND NOT c2.is_merge_commit AND c2.committed_date >= (CURRENT_DATE - '6 mons'::interval)
                  GROUP BY (get_canonical_name(p2.author_name)), c2.project_id) project_commits ON get_canonical_name(p_1.author_name)::text = project_commits.canonical_name::text AND c_1.project_id = project_commits.project_id
          WHERE c_1.author_email IS NOT NULL AND NOT c_1.is_merge_commit AND c_1.committed_date >= (CURRENT_DATE - '6 mons'::interval)
          GROUP BY (get_canonical_name(p_1.author_name))
        )
 SELECT nd.canonical_name,
    nd.original_names,
    nd.email_count,
    nd.username_count,
    nd.all_emails,
    nd.all_usernames,
    count(*) AS total_commits,
    count(DISTINCT c.project_id) AS projects_count,
    count(DISTINCT c.branch_name) AS branches_count,
    count(DISTINCT date(c.committed_date)) AS active_days,
    sum(c.additions) AS total_additions,
    sum(c.deletions) AS total_deletions,
    sum(c.total_changes) AS total_changes,
    min(c.committed_date) AS first_commit_time,
    max(c.committed_date) AS last_commit_time,
    round(
        CASE
            WHEN min(c.committed_date) = max(c.committed_date) THEN count(*)::numeric * 365::numeric
            ELSE count(*)::numeric / GREATEST(EXTRACT(epoch FROM max(c.committed_date) - min(c.committed_date)) / 86400::numeric, 1::numeric) * 365::numeric
        END, 2) AS annual_commit_rate,
    round(
        CASE
            WHEN min(c.committed_date) = max(c.committed_date) THEN count(*)::numeric * 30::numeric
            ELSE count(*)::numeric / GREATEST(EXTRACT(epoch FROM max(c.committed_date) - min(c.committed_date)) / 86400::numeric, 1::numeric) * 30::numeric
        END, 2) AS month_commit_rate,
    round(
        CASE
            WHEN min(c.committed_date) = max(c.committed_date) THEN sum(c.additions)::numeric * 365::numeric
            ELSE sum(c.additions)::numeric / GREATEST(EXTRACT(epoch FROM max(c.committed_date) - min(c.committed_date)) / 86400::numeric, 1::numeric) * 365::numeric
        END, 2) AS annual_total_additions,
    round(
        CASE
            WHEN min(c.committed_date) = max(c.committed_date) THEN sum(c.additions)::numeric * 30::numeric
            ELSE sum(c.additions)::numeric / GREATEST(EXTRACT(epoch FROM max(c.committed_date) - min(c.committed_date)) / 86400::numeric, 1::numeric) * 30::numeric
        END, 2) AS month_total_additions,
    round(sum(c.additions)::numeric / NULLIF(count(*), 0)::numeric, 2) AS avg_additions_per_commit,
    round(sum(c.deletions)::numeric / NULLIF(count(*), 0)::numeric, 2) AS avg_deletions_per_commit,
    round(sum(c.total_changes)::numeric / NULLIF(count(*), 0)::numeric, 2) AS avg_changes_per_commit,
    round(sum(c.files_changed)::numeric / NULLIF(count(*), 0)::numeric, 2) AS avg_files_per_commit,
    round(sum(c.additions)::numeric / NULLIF(sum(c.additions + c.deletions), 0)::numeric * 100::numeric, 2) AS addition_ratio_percent,
    COALESCE(cts.feature_commits, 0::bigint) AS feature_commits,
    COALESCE(cts.bugfix_commits, 0::bigint) AS bugfix_commits,
    COALESCE(cts.doc_commits, 0::bigint) AS doc_commits,
    COALESCE(cts.test_commits, 0::bigint) AS test_commits,
    COALESCE(cts.refactor_commits, 0::bigint) AS refactor_commits,
    COALESCE(cts.style_commits, 0::bigint) AS style_commits,
    COALESCE(cts.chore_commits, 0::bigint) AS chore_commits,
    COALESCE(tds.work_hours_commits, 0::bigint) AS work_hours_commits,
    COALESCE(tds.off_hours_commits, 0::bigint) AS off_hours_commits,
    COALESCE(tds.weekend_commits, 0::bigint) AS weekend_commits,
    COALESCE(tds.weekday_commits, 0::bigint) AS weekday_commits,
    COALESCE(tds.active_hours_count, 0::bigint) AS active_hours_count,
    COALESCE(tds.active_days_of_week, 0::bigint) AS active_days_of_week,
    round(COALESCE(tds.work_hours_commits, 0::bigint)::numeric / NULLIF(count(*), 0)::numeric * 100::numeric, 2) AS work_hours_ratio_percent,
    round(COALESCE(tds.weekend_commits, 0::bigint)::numeric / NULLIF(count(*), 0)::numeric * 100::numeric, 2) AS weekend_ratio_percent,
    COALESCE(pcs.total_projects, 0::bigint) AS total_projects,
    COALESCE(pcs.max_commits_per_project, 0::bigint) AS max_commits_per_project,
    COALESCE(pcs.min_commits_per_project, 0::bigint) AS min_commits_per_project,
    COALESCE(pcs.avg_commits_per_project, 0::numeric) AS avg_commits_per_project,
    COALESCE(pcs.significant_projects, 0::bigint) AS significant_projects,
    COALESCE(pcs.project_names, ''::text) AS project_names,
    round(count(DISTINCT date(c.committed_date))::numeric / NULLIF(GREATEST(EXTRACT(epoch FROM max(c.committed_date) - min(c.committed_date)) / 86400::numeric, 1::numeric), 0::numeric) * 100::numeric, 2) AS consistency_ratio_percent,
    count(DISTINCT c.project_id) AS collaboration_projects_count,
    COALESCE(pcs.avg_commits_per_project, 0::numeric) AS avg_project_contribution,
    round((COALESCE(pcs.max_commits_per_project, 0::bigint) - COALESCE(pcs.min_commits_per_project, 0::bigint))::numeric, 2) AS project_contribution_variance,
    count(DISTINCT EXTRACT(year FROM c.committed_date)) AS active_years_count,
    round(count(DISTINCT c.project_id)::numeric / NULLIF(count(DISTINCT EXTRACT(year FROM c.committed_date)), 0)::numeric, 2) AS projects_per_year,
    round(sum(c.additions)::numeric / NULLIF(count(DISTINCT c.project_id), 0)::numeric, 2) AS avg_additions_per_project,
        CASE
            WHEN count(DISTINCT c.project_id) >= 5 AND count(*) >= 200 THEN 'Tech Lead'::text
            WHEN count(*) >= 100 AND count(DISTINCT c.project_id) >= 3 THEN 'Senior'::text
            WHEN count(*) >= 50 AND count(DISTINCT c.project_id) >= 2 THEN 'Intermediate'::text
            WHEN count(*) >= 10 THEN 'Junior'::text
            ELSE 'Beginner'::text
        END AS contribution_level,
    COALESCE(eq.education, 50) AS education,
    COALESCE(eq.technical_skill, 50) AS technical_skill,
    COALESCE(eq.leadership, 50) AS leadership
   FROM name_dedup_stats nd
     JOIN preferred_names p ON get_canonical_name(p.author_name)::text = nd.canonical_name::text
     JOIN gitlab_commits c ON p.author_email::text = c.author_email::text
     LEFT JOIN commit_type_stats cts ON nd.canonical_name::text = cts.canonical_name::text
     LEFT JOIN time_distribution_stats tds ON nd.canonical_name::text = tds.canonical_name::text
     LEFT JOIN project_contribution_stats pcs ON nd.canonical_name::text = pcs.canonical_name::text
     LEFT JOIN employee_qualifications eq ON nd.canonical_name::text = eq.name
  WHERE c.author_email IS NOT NULL AND NOT c.is_merge_commit AND c.committed_date >= (CURRENT_DATE - '6 mons'::interval)
  GROUP BY nd.canonical_name, nd.original_names, nd.email_count, nd.username_count, nd.all_emails, nd.all_usernames, cts.feature_commits, cts.bugfix_commits, cts.doc_commits, cts.test_commits, cts.refactor_commits, cts.style_commits, cts.chore_commits, tds.work_hours_commits, tds.off_hours_commits, tds.weekend_commits, tds.weekday_commits, tds.active_hours_count, tds.active_days_of_week, pcs.total_projects, pcs.max_commits_per_project, pcs.min_commits_per_project, pcs.avg_commits_per_project, pcs.significant_projects, pcs.project_names, (COALESCE(eq.education, 50)), (COALESCE(eq.technical_skill, 50)), (COALESCE(eq.leadership, 50))
WITH DATA;

-- 为物化视图创建索引以提高查询性能
-- 创建唯一索引以支持CONCURRENTLY刷新
CREATE UNIQUE INDEX IF NOT EXISTS idx_mv_user_contribution_canonical_name_unique ON v_user_contribution_stats(canonical_name);
CREATE INDEX IF NOT EXISTS idx_mv_user_contribution_total_commits ON v_user_contribution_stats(total_commits DESC);
CREATE INDEX IF NOT EXISTS idx_mv_user_contribution_contribution_level ON v_user_contribution_stats(contribution_level);
CREATE INDEX IF NOT EXISTS idx_mv_user_contribution_active_days ON v_user_contribution_stats(active_days DESC);

-- 为半年物化视图创建索引以提高查询性能
-- 创建唯一索引以支持CONCURRENTLY刷新
CREATE UNIQUE INDEX IF NOT EXISTS idx_mv_user_contribution_6m_canonical_name_unique ON v_user_contribution_stats_6m(canonical_name);
CREATE INDEX IF NOT EXISTS idx_mv_user_contribution_6m_total_commits ON v_user_contribution_stats_6m(total_commits DESC);
CREATE INDEX IF NOT EXISTS idx_mv_user_contribution_6m_contribution_level ON v_user_contribution_stats_6m(contribution_level);
CREATE INDEX IF NOT EXISTS idx_mv_user_contribution_6m_active_days ON v_user_contribution_stats_6m(active_days DESC);

-- 创建函数用于刷新物化视图
CREATE OR REPLACE FUNCTION refresh_user_contribution_stats()
RETURNS VOID AS $$
BEGIN
    -- 刷新全量物化视图
    REFRESH MATERIALIZED VIEW CONCURRENTLY v_user_contribution_stats;
    -- 记录刷新时间
    INSERT INTO materialized_view_refresh_log (view_name, refresh_time) 
    VALUES ('v_user_contribution_stats', NOW())
    ON CONFLICT (view_name) DO UPDATE SET refresh_time = NOW();
    
    -- 刷新半年物化视图
    REFRESH MATERIALIZED VIEW CONCURRENTLY v_user_contribution_stats_6m;
    -- 记录刷新时间
    INSERT INTO materialized_view_refresh_log (view_name, refresh_time) 
    VALUES ('v_user_contribution_stats_6m', NOW())
    ON CONFLICT (view_name) DO UPDATE SET refresh_time = NOW();
EXCEPTION
    WHEN OTHERS THEN
        -- 如果并发刷新失败，尝试普通刷新
        REFRESH MATERIALIZED VIEW v_user_contribution_stats;
        INSERT INTO materialized_view_refresh_log (view_name, refresh_time) 
        VALUES ('v_user_contribution_stats', NOW())
        ON CONFLICT (view_name) DO UPDATE SET refresh_time = NOW();
        
        REFRESH MATERIALIZED VIEW v_user_contribution_stats_6m;
        INSERT INTO materialized_view_refresh_log (view_name, refresh_time) 
        VALUES ('v_user_contribution_stats_6m', NOW())
        ON CONFLICT (view_name) DO UPDATE SET refresh_time = NOW();
END;
$$ LANGUAGE plpgsql;

-- 创建单独刷新半年物化视图的函数
CREATE OR REPLACE FUNCTION refresh_user_contribution_stats_6m()
RETURNS VOID AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY v_user_contribution_stats_6m;
    -- 记录刷新时间
    INSERT INTO materialized_view_refresh_log (view_name, refresh_time) 
    VALUES ('v_user_contribution_stats_6m', NOW())
    ON CONFLICT (view_name) DO UPDATE SET refresh_time = NOW();
EXCEPTION
    WHEN OTHERS THEN
        -- 如果并发刷新失败，尝试普通刷新
        REFRESH MATERIALIZED VIEW v_user_contribution_stats_6m;
        INSERT INTO materialized_view_refresh_log (view_name, refresh_time) 
        VALUES ('v_user_contribution_stats_6m', NOW())
        ON CONFLICT (view_name) DO UPDATE SET refresh_time = NOW();
END;
$$ LANGUAGE plpgsql;

-- 创建刷新日志表（如果不存在）
CREATE TABLE IF NOT EXISTS materialized_view_refresh_log (
    view_name VARCHAR(255) PRIMARY KEY,
    refresh_time TIMESTAMP WITH TIME ZONE NOT NULL
);

-- 3. 每日活跃度统计视图
CREATE OR REPLACE VIEW v_daily_activity_stats AS
SELECT
    gitlab_url,
    DATE(commit_time) as commit_date,
    COUNT(*) as total_commits,
    COUNT(DISTINCT author_email) as unique_authors,
    COUNT(DISTINCT project_id) as active_projects,
    SUM(additions) as total_additions,
    SUM(deletions) as total_deletions
FROM gitlab_commits
WHERE NOT is_merge_commit
GROUP BY gitlab_url, DATE(commit_time)
ORDER BY gitlab_url, commit_date DESC;

-- 创建函数用于数据维护

-- 1. 更新每日统计汇总的函数
CREATE OR REPLACE FUNCTION update_daily_stats(target_date DATE DEFAULT CURRENT_DATE, target_gitlab_url VARCHAR(255) DEFAULT NULL)
RETURNS INTEGER AS $$
DECLARE
    affected_rows INTEGER;
BEGIN
    -- 删除目标日期的现有统计
    IF target_gitlab_url IS NULL THEN
        DELETE FROM gitlab_daily_stats WHERE stat_date = target_date;
    ELSE
        DELETE FROM gitlab_daily_stats WHERE stat_date = target_date AND gitlab_url = target_gitlab_url;
    END IF;

    -- 重新计算并插入统计数据
    INSERT INTO gitlab_daily_stats (
        gitlab_url, stat_date, project_branch_key, project_id, project_name, branch_name,
        total_commits, unique_authors, total_additions, total_deletions,
        total_changes, total_files_changed, authors_list
    )
    SELECT
        gitlab_url,
        target_date,
        project_branch_key,
        project_id,
        project_name,
        branch_name,
        COUNT(*) as total_commits,
        COUNT(DISTINCT author_email) as unique_authors,
        SUM(additions) as total_additions,
        SUM(deletions) as total_deletions,
        SUM(total_changes) as total_changes,
        SUM(files_changed) as total_files_changed,
        jsonb_agg(DISTINCT jsonb_build_object(
            'email', author_email,
            'username', author_username,
            'name', author_name,
            'commits', 1
        )) as authors_list
    FROM gitlab_commits
    WHERE DATE(commit_time) = target_date
        AND (target_gitlab_url IS NULL OR gitlab_url = target_gitlab_url)
        AND NOT is_merge_commit
    GROUP BY gitlab_url, project_branch_key, project_id, project_name, branch_name;

    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    RETURN affected_rows;
END;
$$ LANGUAGE plpgsql;

-- 2. 清理旧数据的函数（保留指定天数的数据）
CREATE OR REPLACE FUNCTION cleanup_old_data(retention_days INTEGER DEFAULT 365)
RETURNS INTEGER AS $$
DECLARE
    cutoff_date TIMESTAMPTZ;
    affected_rows INTEGER;
BEGIN
    cutoff_date := NOW() - (retention_days || ' days')::INTERVAL;

    -- 删除旧的提交数据
    DELETE FROM gitlab_commits WHERE commit_time < cutoff_date;
    GET DIAGNOSTICS affected_rows = ROW_COUNT;

    -- 删除旧的每日统计数据
    DELETE FROM gitlab_daily_stats WHERE stat_date < DATE(cutoff_date);

    RETURN affected_rows;
END;
$$ LANGUAGE plpgsql;

-- 创建定时任务（需要pg_cron扩展）
-- CREATE EXTENSION IF NOT EXISTS pg_cron;
-- SELECT cron.schedule('update-daily-stats', '0 1 * * *', 'SELECT update_daily_stats();');
-- SELECT cron.schedule('cleanup-old-data', '0 2 1 * *', 'SELECT cleanup_old_data(365);');
-- SELECT cron.schedule('refresh-user-contribution-stats', '0 * * * *', 'SELECT refresh_user_contribution_stats();'); -- 每小时刷新物化视图

-- 插入初始化数据或配置已移除，不再需要系统级别的同步状态记录

COMMIT;

-- 显示表结构信息
\echo '=== GitLab TimescaleDB 表结构创建完成 ==='
\echo '主要表:'
\echo '1. gitlab_projects - 项目信息表'
\echo '2. gitlab_users - 用户信息表'
\echo '3. gitlab_commits - 提交时序数据表（主表）'
\echo '4. gitlab_branches - 分支信息表'
\echo '5. gitlab_daily_stats - 每日统计汇总表'
\echo '6. gitlab_sync_status - 同步状态表'
\echo ''
\echo '主要视图:'
\echo '1. v_project_branch_stats - 项目分支统计视图'
\echo '2. v_user_contribution_stats - 用户贡献统计物化视图（每小时刷新）'
\echo '3. v_daily_activity_stats - 每日活跃度统计视图'
\echo ''
\echo '主要函数:'
\echo '1. update_daily_stats(date) - 更新每日统计'
\echo '2. cleanup_old_data(days) - 清理旧数据'
\echo '3. refresh_user_contribution_stats() - 刷新用户贡献统计物化视图'